-- =============================================
-- This stored procedure ALTER  another copy of a specified Nav_Menu record
-- and all of its descendants.
-- In addition, it copies associated Tagged Pages.
--
-- Modifications
-- 08/22/2003      E.Tatsui    Created
-- =============================================

CREATE      PROCEDURE amsp_CMCopyNavMenu
    @InOriginalNavMenuID numeric,
  @InContactID numeric,
  @OutNewNavMenuID numeric OUTPUT
AS
BEGIN

  DECLARE
    @Title varchar(255),
    @SectionName varchar(255),
    @NewNavMenuID numeric,
    @ContentID numeric,
    @NavMenuID numeric,
    @Error integer,
    @BelowSortOrder numeric(28,18),
    @LowestSortOrder numeric(28,18),
    @SortIncrement numeric(28,18),
    @SortOrder numeric(28,18),
    @CategoryDepth integer,
    @AncestorNavMenuID numeric,
    @ParentNavMenuID numeric,
    @RowNum integer
  
  CREATE TABLE #NavMenu
               (NavMenuID numeric,
                SortOrder numeric(28,18),
                NewNavMenuID numeric)
  
  -- We want to figure out which Nav_Menu records need to be copied first.
  -- Loop through all the children and put them in a temp table.
  INSERT INTO #NavMenu
  SELECT NavMenuID, SortOrder, NULL
    FROM Nav_Menu WITH (NOLOCK)
   WHERE NavMenuID = @InOriginalNavMenuID

  WHILE @@RowCount > 0 BEGIN
    INSERT INTO #NavMenu
    SELECT NavMenuID, SortOrder, NULL
      FROM Nav_Menu WITH (NOLOCK)
     WHERE ParentNavMenuID IN (SELECT NavMenuID
                                 FROM #NavMenu)
       AND NavMenuID NOT IN (SELECT NavMenuID
                                 FROM #NavMenu)                                
  END

  -- Get all the Nav_Menu records we need to copy:
  DECLARE c_NavMenuToCopy CURSOR FOR
  SELECT a.NavMenuID,
         b.Title,
         b.CategoryDepth,
         b.AncestorNavMenuID,
         b.ParentNavMenuID
    FROM #NavMenu a, Nav_Menu b WITH (NOLOCK)
   WHERE a.NavMenuID = b.NavMenuID
   ORDER BY b.SortOrder

  SELECT @LowestSortOrder = Max(SortOrder)
    FROM #NavMenu

  SELECT @BelowSortOrder = Min(SortOrder)
    FROM Nav_Menu WITH (NOLOCK)
   WHERE SortOrder > @LowestSortOrder
     AND NavContentGroupInd = (SELECT NavContentGroupInd FROM Nav_Menu WHERE NavMenuID = @InOriginalNavMenuID)

  SELECT @RowNum = COUNT(*)
    FROM #NavMenu

  IF @BelowSortOrder IS NULL
    SET @BelowSortOrder = @LowestSortOrder + @RowNum

  -- Figure out starting sort order and increment.
  SET @SortIncrement = (@BelowSortOrder - @LowestSortOrder) / (@RowNum + 1)
  SET @SortOrder = @LowestSortOrder

  OPEN c_NavMenuToCopy
  FETCH NEXT FROM  c_NavMenuToCopy
        INTO @NavMenuID,
             @Title,
             @CategoryDepth,
             @AncestorNavMenuID,
             @ParentNavMenuID

  WHILE @@FETCH_STATUS = 0 BEGIN

    -- For the first row, add "Copy of" to the title
    IF @NewNavMenuID IS NULL
      SET @Title = 'Copy of ' + @Title

    SET @Title = Left(@Title,255)
    
    EXEC amsp_CMGetUniqueSectionName NULL, @Title, @SectionName OUTPUT

    SET @SortOrder = @SortOrder + @SortIncrement

    -- If the parent is also copied previously, use the new ParentNavMenuID
    IF @ParentNavMenuID IS NOT NULL
      SELECT @ParentNavMenuID = NewNavMenuID
        FROM #NavMenu
       WHERE NavMenuID = @ParentNavMenuID

    -- If the ancestor is also copied previously, use the new ParentNavMenuID
    IF @AncestorNavMenuID IS NOT NULL
      SELECT @AncestorNavMenuID = NewNavMenuID
        FROM #NavMenu
       WHERE NavMenuID = @AncestorNavMenuID

    -- We'd create a record at the end as the 1st level,
    -- and move it to appropriate position later.
    INSERT INTO Nav_Menu
                (WorkflowStatusCode,
                 HideFlag,
                 NavContentGroupInd,
                 Name,
                 Title,
                 ParentNavMenuID,
                 AncestorNavMenuID,
                 CategoryDepth,
                 SortOrder,
                 DirectListComboInd,
                 ContentAuthorityGroupID,
                 AuthoritySetManuallyFlag,
                 OwnerContactID,
                 OwnerSetManuallyFlag,
                 ExpirationDays,
                 LastUpdatedByContactID,
                 WebsiteKey,
                 ShowInTopFlag,
                 ShowInSideFlag,
                 MicrositeFlag)
    SELECT 'W',
           HideFlag,
           NavContentGroupInd,
           @SectionName,
           @Title,
           @ParentNavMenuID,
           @AncestorNavMenuID,
           CategoryDepth,
           @SortOrder,
           DirectListComboInd,
           ContentAuthorityGroupID,
           AuthoritySetManuallyFlag,
           OwnerContactID,
           OwnerSetManuallyFlag,
           ExpirationDays,
           @InContactID,
           WebsiteKey,
           ShowInTopFlag,
           ShowInSideFlag,
           MicrositeFlag
      FROM Nav_Menu WITH (NOLOCK)
     WHERE NavMenuID = @NavMenuID

    SELECT @Error = @@Error

    IF @Error <> 0 BEGIN
      RETURN
    END
             
    SET @NewNavMenuID = @@Identity

    -- Save the new NavMenuID
    UPDATE #NavMenu
       SET NewNavMenuID = @NewNavMenuID
     WHERE NavMenuID = @NavMenuID

    -- We want to return the 1st NavMenuID.
    IF @OutNewNavMenuID IS NULL
      SET @OutNewNavMenuID = @NewNavMenuID

    -- If we didn't specify AncestorID on insertion, set it now.
    IF @AncestorNavMenuID IS NULL
      UPDATE Nav_Menu
         SET AncestorNavMenuID = @NewNavMenuID
       WHERE NavMenuID = @NewNavMenuID
    
    INSERT INTO Nav_Menu_Workflow_Log
                (NavMenuID,
                 WorkflowStatusCode,
                 ContactID,
                 ChangeDateTime)
     VALUES (@NewNavMenuID,
             'W',
             @InContactID,
             CURRENT_TIMESTAMP)

    -- Copy BreadCrumb
    INSERT INTO Nav_Menu_Feature
                (NavMenuID,
                 BreadCrumb)
    SELECT @NewNavMenuID,
           @Title
      FROM Nav_Menu_Feature
     WHERE NavMenuID = @InOriginalNavMenuID

    -- Figure out FilePath, DescendantCount and AncestoryList
    EXEC amsp_CMUpdateNavProperties @NewNavMenuID

    /* Comment this out until we actually need it
    -- Copy all the tagged page associated with the original Nav_Menu record
    DECLARE c_CopyContent CURSOR FOR
     SELECT a.ContentID
       FROM  vCurrent_Content a, Content_Link b
      WHERE a.ContentID = b.ContentID
        AND a.NavMenuID = @InOriginalNavMenuID
        AND b.TaggedPageLayoutID IS NOT NULL
        AND a.WorkflowStatusCode IN ('A','P','W','E')
  
    OPEN c_CopyContent
    FETCH NEXT FROM c_CopyContent
     INTO @ContentID  
    
    WHILE @@FETCH_STATUS = 0 BEGIN
      EXEC amsp_CMCopyContentAsNew @ContentID, @InContactID, @NewNavMenuID, NULL
  
      FETCH NEXT FROM c_CopyContent
       INTO @ContentID  
    END
  
    SELECT @Error = @@Error
    IF @Error <> 0 BEGIN
      RETURN
    END
  
    CLOSE c_CopyContent
    DEALLOCATE c_CopyContent
    -- Ends copying all the tagged page.

    */

    FETCH NEXT FROM  c_NavMenuToCopy
          INTO @NavMenuID,
               @Title,
               @CategoryDepth,
               @AncestorNavMenuID,
               @ParentNavMenuID
  END -- Ends looping through Nav_Menu records to copy.

  CLOSE c_NavMenuToCopy
  DEALLOCATE c_NavMenuToCopy

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMCopyNavMenu] TO [IMIS]
GO